from pandas.core.tools.datetimes import Scalar
import requests
import datetime
import pandas as pd
import pymysql
from joblib import dump
from sklearn.preprocessing import MinMaxScaler

class WeatherUtils(object):
    def __init__(self):
        self.date = [
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]
        self.url = 'http://gfeljm.tianqiapi.com/api'

    def get_date(self):
        """获取天气数据
        """
        data_list = []
        for d in self.date:
            conf = {
                "appid": "13954192",
                "appsecret": "BA7zIjrm",
                "version": "history",
                "year": d[:4],
                "month": d[5:7],
                "city": "南昌"

            }
            res = requests.get(self.url+'?', params=conf)
            res_data = res.json()
            #print(res_data)
            for i in res_data['data']:
                data_list.append({
                    'date': datetime.datetime.strptime(i['ymd'], '%Y-%m-%d'),
                    'bWendu': i['bWendu'],
                    'yWendu': i['yWendu'],
                    'tianqi': i['tianqi'],
                    'fengli': i['fengli'],
                })

        df = pd.DataFrame(data_list)
        df.to_csv('./timing/weather.csv')
        return df

class MysqlUtils(object):
    def __init__(self):
        self.weather_data = []
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='sjk1234',
            db='scenic',
            port=3306,
            charset='utf8'
        )
    def is_holiday(self, date):
        """是否节假日判断
        """
        if str(date) in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03','2024-10-04',
                     '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2025-01-02', '2025-01-03', '2025-01-03']:
            return 1
        return 0
    def get_scenic_data(self):
        """获取数据
        """
        # 获取天气数据
        wu = WeatherUtils()
        self.weather_data = wu.get_date()

        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT DATE(g.create_time) as date,
        count(*) as count FROM order_user_gate_rel g
        WHERE DATE(g.create_time) < '2025-01-01'
        GROUP BY date
        """
        cursor.execute(sql)
        ret = cursor.fetchall()

        df = pd.DataFrame(ret)
        df['date'] = pd.to_datetime(df['date'])
        df_pivot = pd.merge(self.weather_data, df, on='date')
        print(df_pivot)
        df_pivot.set_index('date', inplace=True)
        df_pivot['bWendu'] = df_pivot['bWendu'].str.replace('o', '').astype(int)
        df_pivot['yWendu'] = df_pivot['yWendu'].str.replace('o', '').astype(int)


        df_pivot['dow'] = df_pivot.index.dayofweek
        df_pivot['month'] = df_pivot.index.month
        #print(df_pivot)
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
        # 对星期几
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month', 'tianqi', 'fengli'], dtype=int)

        #归一化
        scaler = MinMaxScaler()
        features = df_pivot[['count']]
        df_pivot['count'] = scaler.fit_transform(features)
        #归一化天气
        weather_features = df_pivot[['bWendu', 'yWendu']]
        df_pivot[['bWendu', 'yWendu']]  = scaler.fit_transform(weather_features)
        #print(df_pivot)

        df_pivot.to_csv('timing/scenic_data.csv', index=False)


if __name__== '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()
    